const express = require('express');
const bcrypt = require('bcryptjs');
const jwt = require('jsonwebtoken');
const { promisePool } = require('../config/database');
const { registerSchema, loginSchema, resetPasswordSchema, validateUserSchema, updateProfileSchema, bindEmailSchema, unbindEmailSchema, disableUserSchema, getUsersSchema, adminUpdateUserSchema, deleteUserSchema } = require('../schema/auth');

const router = express.Router();

// JWT密钥配置（生产环境应该使用环境变量）
const JWT_SECRET = process.env.JWT_SECRET || 'wangwang';
const JWT_EXPIRES_IN = '7d'; // token有效期7天

// 用户注册接口
router.post('/register', async (req, res) => {
  try {
    // 验证请求数据
    const { error, value } = registerSchema.validate(req.body);
    if (error) {
      return res.status(400).json({
        success: false,
        message: error.details[0].message
      });
    }

    const { username, password, role } = value;

    // 检查用户名是否已存在
    const [existingUsers] = await promisePool.execute(
      'SELECT id FROM users WHERE username = ? AND deleted_at IS NULL',
      [username]
    );

    if (existingUsers.length > 0) {
      return res.status(400).json({
        success: false,
        message: '用户名已存在'
      });
    }

    // 生成用户账号（年份+6位序号）
    const currentYear = new Date().getFullYear();
    const [maxAccountResult] = await promisePool.execute(
      `SELECT MAX(CAST(SUBSTRING(account_number, 5) AS UNSIGNED)) as max_num 
       FROM users 
       WHERE account_number LIKE ? AND deleted_at IS NULL`,
      [`${currentYear}%`]
    );
    
    const maxNum = maxAccountResult[0].max_num || 0;
    const accountNumber = `${currentYear}${String(maxNum + 1).padStart(6, '0')}`;

    // 加密密码
    const saltRounds = 10;
    const hashedPassword = await bcrypt.hash(password, saltRounds);

    // 插入新用户
    const [result] = await promisePool.execute(
      `INSERT INTO users (username, account_number, password, role, status, created_at, updated_at) 
       VALUES (?, ?, ?, ?, 'active', NOW(), NOW())`,
      [username, accountNumber, hashedPassword, role]
    );

    if (result.affectedRows > 0) {
      // 生成JWT token（注册成功后自动登录）
      const token = jwt.sign(
        {
          userId: result.insertId,
          username: username,
          role: role
        },
        JWT_SECRET,
        {
          expiresIn: JWT_EXPIRES_IN
        }
      );

      res.json({
        success: true,
        message: '注册成功',
        data: {
          userId: result.insertId,
          username: username,
          accountNumber: accountNumber,
          role: role,
          token: token // 返回token，实现注册后自动登录
        }
      });
    } else {
      res.status(500).json({
        success: false,
        message: '注册失败，请重试'
      });
    }

  } catch (error) {
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});


// 用户登录接口
router.post('/login', async (req, res) => {
  try {
    // 验证请求数据
    const { error, value } = loginSchema.validate(req.body);
    if (error) {
      return res.status(400).json({
        success: false,
        message: error.details[0].message
      });
    }

    const { loginIdentifier, password, role } = value;

    // 查询用户信息 - 支持用户名、账号、手机号登录，包含完整的个人信息
    const [users] = await promisePool.execute(
      `SELECT id, username, account_number, password, role, name, email, phone, avatar, status, 
              id_card, gender, birth_date, province, city, county, village, address, postal_code,
              last_login_time, login_count, created_at
       FROM users 
       WHERE (username = ? OR account_number = ? OR phone = ?) AND role = ? AND deleted_at IS NULL`,
      [loginIdentifier, loginIdentifier, loginIdentifier, role]
    );

    if (users.length === 0) {
      return res.status(401).json({
        success: false,
        message: '登录标识或密码错误'
      });
    }

    const user = users[0];

    // 检查账户状态
    if (user.status !== 'active') {
      return res.status(401).json({
        success: false,
        message: '账户已被禁用或已注销'
      });
    }

    // 验证密码
    const isPasswordValid = await bcrypt.compare(password, user.password);
    if (!isPasswordValid) {
      return res.status(401).json({
        success: false,
        message: '登录标识或密码错误'
      });
    }

    // 更新登录信息
    await promisePool.execute(
      `UPDATE users 
       SET last_login_time = NOW(), login_count = login_count + 1, updated_at = NOW()
       WHERE id = ?`,
      [user.id]
    );

    // 生成JWT token
    const token = jwt.sign(
      {
        userId: user.id,
        username: user.username,
        role: user.role
      },
      JWT_SECRET,
      {
        expiresIn: JWT_EXPIRES_IN
      }
    );

    // 返回用户信息（不包含密码），包含完整的个人信息
    const userInfo = {
      id: user.id,
      username: user.username,
      accountNumber: user.account_number,
      role: user.role,
      name: user.name,
      email: user.email,
      phone: user.phone,
      avatar: user.avatar,
      // 添加完整的个人信息字段
      idCard: user.id_card,
      gender: user.gender,
      birthDate: user.birth_date,
      province: user.province,
      city: user.city,
      county: user.county,
      village: user.village,
      address: user.address,
      postalCode: user.postal_code,
      lastLoginTime: user.last_login_time,
      loginCount: user.login_count + 1,
      createdAt: user.created_at
    };

    res.json({
      success: true,
      message: '登录成功',
      data: {
        ...userInfo,
        token: token // 返回token
      }
    });

  } catch (error) {
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});


// 重置密码接口
router.post('/reset-password', async (req, res) => {
  try {
    // 验证请求数据
    const { error, value } = resetPasswordSchema.validate(req.body);

    if (error) {
      return res.status(400).json({
        success: false,
        message: error.details[0].message
      });
    }

    const { loginIdentifier, oldPassword, newPassword, confirmPassword } = value;

    // 查询用户信息 - 支持用户名、账号、手机号
    const [users] = await promisePool.execute(
      `SELECT id, username, account_number, password, role, status
       FROM users 
       WHERE (username = ? OR account_number = ? OR phone = ?) AND deleted_at IS NULL`,
      [loginIdentifier, loginIdentifier, loginIdentifier]
    );

    if (users.length === 0) {
      return res.status(401).json({
        success: false,
        message: '用户不存在'
      });
    }

    const user = users[0];

    // 验证新密码与确认密码是否一致
    if (newPassword !== confirmPassword) {
      return res.status(400).json({
        success: false,
        message: '新密码与确认密码不一致'
      });
    }

    // 如果提供了原密码，验证原密码是否正确
    if (oldPassword) {
      const isOldPasswordValid = await bcrypt.compare(oldPassword, user.password);
      if (!isOldPasswordValid) {
        return res.status(401).json({
          success: false,
          message: '原密码错误'
        });
      }

      // 验证新密码是否与原密码相同
      if (newPassword === oldPassword) {
        return res.status(400).json({
          success: false,
          message: '新密码不能与原密码相同'
        });
      }
    }

    // 检查账户状态
    if (user.status !== 'active') {
      return res.status(401).json({
        success: false,
        message: '账户已被禁用或未激活'
      });
    }

    // 加密新密码
    const saltRounds = 10;
    const hashedNewPassword = await bcrypt.hash(newPassword, saltRounds);

    // 更新密码
    const [result] = await promisePool.execute(
      `UPDATE users 
       SET password = ?, updated_at = NOW()
       WHERE id = ?`,
      [hashedNewPassword, user.id]
    );

    if (result.affectedRows > 0) {
      res.json({
        success: true,
        message: '密码修改成功'
      });
    } else {
      res.status(500).json({
        success: false,
        message: '密码修改失败，请重试'
      });
    }

  } catch (error) {
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});


// 校验用户是否存在接口
router.post('/validate-user', async (req, res) => {
  try {
    // 验证请求数据
    const { error, value } = validateUserSchema.validate(req.body);
    if (error) {
      return res.status(400).json({
        success: false,
        message: error.details[0].message
      });
    }

    const { loginIdentifier } = value;

    // 查询用户是否存在 - 支持用户名、账号、手机号
    const [users] = await promisePool.execute(
      `SELECT id, username, account_number, phone, status
       FROM users 
       WHERE (username = ? OR account_number = ? OR phone = ?) AND deleted_at IS NULL`,
      [loginIdentifier, loginIdentifier, loginIdentifier]
    );

    if (users.length === 0) {
      return res.json({
        success: true,
        message: '用户不存在',
        data: {
          exists: false
        }
      });
    }

    const user = users[0];

    // 检查账户状态
    if (user.status !== 'active') {
      return res.json({
        success: true,
        message: '用户存在但账户已被禁用',
        data: {
          exists: true,
          status: user.status
        }
      });
    }

    res.json({
      success: true,
      message: '用户存在',
      data: {
        exists: true,
        status: user.status
      }
    });

  } catch (error) {
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});


// 修改个人信息接口
router.post('/update-profile', async (req, res) => {
  try {
    // 验证请求数据
    const { error, value } = updateProfileSchema.validate(req.body);
    if (error) {
      return res.status(400).json({
        success: false,
        message: error.details[0].message
      });
    }

    const { 
      loginIdentifier, 
      name, 
      idCard, 
      gender, 
      birthDate, 
      phone, 
      province, 
      city, 
      county, 
      village, 
      address, 
      postalCode, 
      avatar 
    } = req.body;

    // 查询用户信息
    const [users] = await promisePool.execute(
      `SELECT id, username, account_number, phone, status
       FROM users 
       WHERE (username = ? OR account_number = ? OR phone = ?) AND deleted_at IS NULL`,
      [loginIdentifier, loginIdentifier, loginIdentifier]
    );

    if (users.length === 0) {
      return res.status(404).json({
        success: false,
        message: '用户不存在'
      });
    }

    const user = users[0];

    // 检查账户状态
    if (user.status !== 'active') {
      return res.status(401).json({
        success: false,
        message: '账户已被禁用或未激活'
      });
    }

    // 如果更新手机号，检查新手机号是否已被其他用户使用
    if (phone && phone !== user.phone) {
      const [existingPhone] = await promisePool.execute(
        'SELECT id FROM users WHERE phone = ? AND id != ? AND deleted_at IS NULL',
        [phone, user.id]
      );

      if (existingPhone.length > 0) {
        return res.status(400).json({
          success: false,
          message: '该手机号已被其他用户使用'
        });
      }
    }

    // 更新用户信息
    const [result] = await promisePool.execute(
      `UPDATE users 
       SET name = ?, id_card = ?, gender = ?, birth_date = ?, phone = ?, 
           province = ?, city = ?, county = ?, village = ?, address = ?, 
           postal_code = ?, avatar = ?, updated_at = NOW()
       WHERE id = ?`,
      [
        name || null,
        idCard || null,
        gender || null,
        birthDate || null,
        phone || null,
        province || null,
        city || null,
        county || null,
        village || null,
        address || null,
        postalCode || null,
        avatar || null,
        user.id
      ]
    );

    if (result.affectedRows > 0) {
      res.json({
        success: true,
        message: '个人信息修改成功',
        data: {
          userId: user.id,
          accountNumber: user.account_number, // 添加 accountNumber 字段
          name: name || null,
          idCard: idCard || null,
          gender: gender || null,
          birthDate: birthDate || null,
          phone: phone || null,
          province: province || null,
          city: city || null,
          county: county || null,
          village: village || null,
          address: address || null,
          postalCode: postalCode || null,
          avatar: avatar || null
        }
      });
    } else {
      res.status(500).json({
        success: false,
        message: '个人信息修改失败，请重试'
      });
    }

  } catch (error) {
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});


// 绑定邮箱接口
router.post('/bind-email', async (req, res) => {
  try {
    // 验证请求数据
    const { error, value } = bindEmailSchema.validate(req.body);
    if (error) {
      return res.status(400).json({
        success: false,
        message: error.details[0].message
      });
    }

    // const { loginIdentifier, email, verificationCode, password } = value;
    const { loginIdentifier, email, password } = value;

    // 查询用户信息 - 支持用户名、账号、手机号
    const [users] = await promisePool.execute(
      `SELECT id, username, account_number, password, role, status, email
       FROM users 
       WHERE (username = ? OR account_number = ? OR phone = ?) AND deleted_at IS NULL`,
      [loginIdentifier, loginIdentifier, loginIdentifier]
    );

    if (users.length === 0) {
      return res.status(404).json({
        success: false,
        message: '用户不存在'
      });
    }

    const user = users[0];

    // 检查账户状态
    if (user.status !== 'active') {
      return res.status(401).json({
        success: false,
        message: '账户已被禁用或未激活'
      });
    }

    // 验证密码
    const isPasswordValid = await bcrypt.compare(password, user.password);
    if (!isPasswordValid) {
      return res.status(401).json({
        success: false,
        message: '密码错误'
      });
    }

    // 检查邮箱是否已被其他用户使用
    const [existingEmail] = await promisePool.execute(
      'SELECT id FROM users WHERE email = ? AND id != ? AND deleted_at IS NULL',
      [email, user.id]
    );

    if (existingEmail.length > 0) {
      return res.status(400).json({
        success: false,
        message: '该邮箱已被其他用户使用'
      });
    }

    // 检查是否与当前邮箱相同
    if (user.email && user.email === email) {
      return res.status(400).json({
        success: false,
        message: '新邮箱不能与当前邮箱相同'
      });
    }


    // 更新用户邮箱
    const [result] = await promisePool.execute(
      `UPDATE users 
       SET email = ?, updated_at = NOW()
       WHERE id = ?`,
      [email, user.id]
    );

    if (result.affectedRows > 0) {
      res.json({
        success: true,
        message: user.email ? '邮箱更换成功' : '邮箱绑定成功',
        data: {
          userId: user.id,
          accountNumber: user.account_number,
          email: email
        }
      });
    } else {
      res.status(500).json({
        success: false,
        message: '邮箱绑定失败，请重试'
      });
    }

  } catch (error) {
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});


// 解绑邮箱接口
router.post('/unbind-email', async (req, res) => {
  try {
    // 验证请求数据
    const { error, value } = unbindEmailSchema.validate(req.body);
    if (error) {
      return res.status(400).json({
        success: false,
        message: error.details[0].message
      });
    }

    const { loginIdentifier, password } = value;

    // 查询用户信息 - 支持用户名、账号、手机号
    const [users] = await promisePool.execute(
      `SELECT id, username, account_number, password, role, status, email
       FROM users 
       WHERE (username = ? OR account_number = ? OR phone = ?) AND deleted_at IS NULL`,
      [loginIdentifier, loginIdentifier, loginIdentifier]
    );

    if (users.length === 0) {
      return res.status(404).json({
        success: false,
        message: '用户不存在'
      });
    }

    const user = users[0];

    // 检查账户状态
    if (user.status !== 'active') {
      return res.status(401).json({
        success: false,
        message: '账户已被禁用或未激活'
      });
    }

    // 验证密码
    const isPasswordValid = await bcrypt.compare(password, user.password);
    if (!isPasswordValid) {
      return res.status(401).json({
        success: false,
        message: '密码错误'
      });
    }

    // 检查用户是否已绑定邮箱
    if (!user.email) {
      return res.status(400).json({
        success: false,
        message: '您尚未绑定邮箱'
      });
    }

    // 解绑用户邮箱
    const [result] = await promisePool.execute(
      `UPDATE users 
       SET email = NULL, updated_at = NOW()
       WHERE id = ?`,
      [user.id]
    );

    if (result.affectedRows > 0) {
      res.json({
        success: true,
        message: '邮箱解绑成功',
        data: {
          userId: user.id,
          accountNumber: user.account_number
        }
      });
    } else {
      res.status(500).json({
        success: false,
        message: '邮箱解绑失败，请重试'
      });
    }

  } catch (error) {
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});

// 获取用户列表接口（排除管理员）
router.get('/list', async (req, res) => {
  try {
    // 验证请求参数
    const { error, value } = getUsersSchema.validate(req.query);
    if (error) {
      return res.status(400).json({
        success: false,
        message: error.details[0].message
      });
    }

    const { page, pageSize, keyword, status, sortBy, sortOrder } = value;
    const offset = (page - 1) * pageSize;

    // 构建查询条件
    let whereConditions = ['role != ?', 'deleted_at IS NULL'];
    let queryParams = ['admin'];

    // 状态筛选
    if (status !== 'all') {
      if (status === 'active') {
        // 活跃用户筛选条件：
        // 1. 状态必须为active
        // 2. 满足以下任一条件：
        //    - 最后登录时间不超过一周 (last_login_time >= DATE_SUB(NOW(), INTERVAL 7 DAY))
        //    - 登录次数超过10次(含10次) (login_count >= 10)
        whereConditions.push('status = ? AND (last_login_time >= DATE_SUB(NOW(), INTERVAL 7 DAY) OR login_count >= 10)');
        queryParams.push(status);
      } else {
        whereConditions.push('status = ?');
        queryParams.push(status);
      }
    }

    // 关键词搜索
    if (keyword && keyword.trim()) {
      whereConditions.push('(name LIKE ? OR phone LIKE ? OR email LIKE ? OR username LIKE ?)');
      const searchPattern = `%${keyword.trim()}%`;
      queryParams.push(searchPattern, searchPattern, searchPattern, searchPattern);
    }

    const whereClause = whereConditions.join(' AND ');

    // 构建排序
    const orderBy = `${sortBy} ${sortOrder.toUpperCase()}`;

    // 查询用户列表
    const [users] = await promisePool.execute(
      `SELECT 
        id, username, account_number, name, email, phone, avatar, 
        id_card, gender, birth_date, province, city, county, village, address, postal_code,
        status, last_login_time, login_count, created_at, updated_at
       FROM users 
       WHERE ${whereClause}
       ORDER BY ${orderBy}
       LIMIT ${pageSize} OFFSET ${offset}`,
      queryParams
    );

    // 查询总数
    const [countResult] = await promisePool.execute(
      `SELECT COUNT(*) as total FROM users WHERE ${whereClause}`,
      queryParams
    );

    const total = countResult[0].total;
    const totalPages = Math.ceil(total / pageSize);

    // 格式化用户数据
    const formattedUsers = users.map(user => ({
      id: user.id,
      username: user.username,
      accountNumber: user.account_number,
      name: user.name,
      email: user.email,
      phone: user.phone,
      avatar: user.avatar,
      idCard: user.id_card,
      gender: user.gender,
      birthDate: user.birth_date,
      province: user.province,
      city: user.city,
      county: user.county,
      village: user.village,
      address: user.address,
      postalCode: user.postal_code,
      status: user.status,
      statusText: user.status === 'active' ? '正常' : user.status === 'inactive' ? '未激活' : '已禁用',
      lastLoginTime: user.last_login_time,
      loginCount: user.login_count,
      createdAt: user.created_at,
      updatedAt: user.updated_at
    }));

    res.json({
      success: true,
      message: '获取用户列表成功',
      data: {
        users: formattedUsers,
        pagination: {
          currentPage: page,
          pageSize: pageSize,
          total: total,
          totalPages: totalPages,
          hasNextPage: page < totalPages,
          hasPrevPage: page > 1
        }
      }
    });

  } catch (error) {
    console.error('获取用户列表失败:', error);
    console.error('错误详情:', {
      message: error.message,
      code: error.code,
      errno: error.errno,
      sqlState: error.sqlState,
      sqlMessage: error.sqlMessage,
      stack: error.stack
    });
    
    // 根据错误类型返回不同的错误信息
    let errorMessage = '服务器内部错误';
    if (error.code === 'ER_NO_SUCH_TABLE') {
      errorMessage = '数据表不存在，请检查数据库配置';
    } else if (error.code === 'ER_ACCESS_DENIED_ERROR') {
      errorMessage = '数据库访问被拒绝，请检查数据库连接配置';
    } else if (error.code === 'ECONNREFUSED') {
      errorMessage = '无法连接到数据库服务器';
    } else if (error.code === 'ER_BAD_DB_ERROR') {
      errorMessage = '数据库不存在，请先创建数据库';
    }
    
    res.status(500).json({
      success: false,
      message: errorMessage,
      error: process.env.NODE_ENV === 'development' ? {
        message: error.message,
        code: error.code,
        stack: error.stack
      } : undefined
    });
  }
});

// 获取用户统计信息接口
router.get('/users/stats', async (req, res) => {
  try {
    // 查询各种状态的用户数量（排除管理员）
    const [statsResult] = await promisePool.execute(
      `SELECT 
        COUNT(*) as totalUsers,
        SUM(CASE WHEN status = 'active' AND (
          last_login_time >= DATE_SUB(NOW(), INTERVAL 7 DAY) OR 
          login_count > 10
        ) THEN 1 ELSE 0 END) as activeUsers,
        SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) as inactiveUsers,
        SUM(CASE WHEN status = 'banned' THEN 1 ELSE 0 END) as bannedUsers,
        SUM(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 ELSE 0 END) as newUsers
       FROM users 
       WHERE role != 'admin' AND deleted_at IS NULL`
    );

    const stats = statsResult[0];

    res.json({
      success: true,
      message: '获取用户统计信息成功',
      data: {
        totalUsers: parseInt(stats.totalUsers),
        activeUsers: parseInt(stats.activeUsers),
        inactiveUsers: parseInt(stats.inactiveUsers),
        bannedUsers: parseInt(stats.bannedUsers),
        newUsers: parseInt(stats.newUsers)
      }
    });

  } catch (error) {
    console.error('获取用户统计信息失败:', error);
    console.error('错误详情:', {
      message: error.message,
      code: error.code,
      errno: error.errno,
      sqlState: error.sqlState,
      sqlMessage: error.sqlMessage,
      stack: error.stack
    });
    
    // 根据错误类型返回不同的错误信息
    let errorMessage = '服务器内部错误';
    if (error.code === 'ER_NO_SUCH_TABLE') {
      errorMessage = '数据表不存在，请检查数据库配置';
    } else if (error.code === 'ER_ACCESS_DENIED_ERROR') {
      errorMessage = '数据库访问被拒绝，请检查数据库连接配置';
    } else if (error.code === 'ECONNREFUSED') {
      errorMessage = '无法连接到数据库服务器';
    } else if (error.code === 'ER_BAD_DB_ERROR') {
      errorMessage = '数据库不存在，请先创建数据库';
    }
    
    res.status(500).json({
      success: false,
      message: errorMessage,
      error: process.env.NODE_ENV === 'development' ? {
        message: error.message,
        code: error.code,
        stack: error.stack
      } : undefined
    });
  }
});

// 获取用户总数量接口（按角色分类）
router.get('/users/count', async (req, res) => {
  try {
    // 查询管理员和村民的用户数量
    const [countResult] = await promisePool.execute(
      `SELECT 
        role,
        COUNT(*) as count
       FROM users 
       WHERE deleted_at IS NULL
       GROUP BY role`
    );

    // 查询今日新增用户数量
    const [todayNewUsersResult] = await promisePool.execute(
      `SELECT 
        role,
        COUNT(*) as count
       FROM users 
       WHERE deleted_at IS NULL 
         AND DATE(created_at) = CURDATE()
       GROUP BY role`
    );

    // 初始化统计数据
    let adminCount = 0;
    let citizenCount = 0;
    let todayNewAdminCount = 0;
    let todayNewCitizenCount = 0;

    // 处理总用户数量查询结果
    countResult.forEach(row => {
      if (row.role === 'admin') {
        adminCount = parseInt(row.count);
      } else if (row.role === 'citizen') {
        citizenCount = parseInt(row.count);
      }
    });

    // 处理今日新增用户查询结果
    todayNewUsersResult.forEach(row => {
      if (row.role === 'admin') {
        todayNewAdminCount = parseInt(row.count);
      } else if (row.role === 'citizen') {
        todayNewCitizenCount = parseInt(row.count);
      }
    });

    res.json({
      success: true,
      message: '获取用户总数量成功',
      data: {
        // 总用户数量
        adminCount: adminCount,
        citizenCount: citizenCount,
        totalCount: adminCount + citizenCount,
        // 今日新增用户数量
        todayNewAdminCount: todayNewAdminCount,
        todayNewCitizenCount: todayNewCitizenCount,
        todayNewTotalCount: todayNewAdminCount + todayNewCitizenCount
      }
    });

  } catch (error) {
    console.error('获取用户总数量失败:', error);
    console.error('错误详情:', {
      message: error.message,
      code: error.code,
      errno: error.errno,
      sqlState: error.sqlState,
      sqlMessage: error.sqlMessage,
      stack: error.stack
    });
    
    // 根据错误类型返回不同的错误信息
    let errorMessage = '服务器内部错误';
    if (error.code === 'ER_NO_SUCH_TABLE') {
      errorMessage = '数据表不存在，请检查数据库配置';
    } else if (error.code === 'ER_ACCESS_DENIED_ERROR') {
      errorMessage = '数据库访问被拒绝，请检查数据库连接配置';
    } else if (error.code === 'ECONNREFUSED') {
      errorMessage = '无法连接到数据库服务器';
    } else if (error.code === 'ER_BAD_DB_ERROR') {
      errorMessage = '数据库不存在，请先创建数据库';
    }
    
    res.status(500).json({
      success: false,
      message: errorMessage,
      error: process.env.NODE_ENV === 'development' ? {
        message: error.message,
        code: error.code,
        stack: error.stack
      } : undefined
    });
  }
});

// 管理员修改用户信息接口
router.post('/admin/update-user', async (req, res) => {
  try {
    // 验证请求数据
    const { error, value } = adminUpdateUserSchema.validate(req.body);
    if (error) {
      return res.status(400).json({
        success: false,
        message: error.details[0].message
      });
    }

    const { 
      userId, 
      name, 
      idCard, 
      gender, 
      birthDate, 
      phone, 
      email, 
      province, 
      city, 
      county, 
      village, 
      address, 
      postalCode, 
      status 
    } = value;

    // 查询用户是否存在
    const [users] = await promisePool.execute(
      'SELECT id, username, account_number, phone, email, status FROM users WHERE id = ? AND deleted_at IS NULL',
      [userId]
    );

    if (users.length === 0) {
      return res.status(404).json({
        success: false,
        message: '用户不存在'
      });
    }

    const user = users[0];

    // 如果更新手机号，检查新手机号是否已被其他用户使用
    if (phone && phone !== user.phone) {
      const [existingPhone] = await promisePool.execute(
        'SELECT id FROM users WHERE phone = ? AND id != ? AND deleted_at IS NULL',
        [phone, userId]
      );

      if (existingPhone.length > 0) {
        return res.status(400).json({
          success: false,
          message: '该手机号已被其他用户使用'
        });
      }
    }

    // 如果更新邮箱，检查新邮箱是否已被其他用户使用
    if (email && email !== user.email) {
      const [existingEmail] = await promisePool.execute(
        'SELECT id FROM users WHERE email = ? AND id != ? AND deleted_at IS NULL',
        [email, userId]
      );

      if (existingEmail.length > 0) {
        return res.status(400).json({
          success: false,
          message: '该邮箱已被其他用户使用'
        });
      }
    }

    // 构建更新字段和值
    const updateFields = [];
    const updateValues = [];

    if (name !== undefined) {
      updateFields.push('name = ?');
      updateValues.push(name || null);
    }
    if (idCard !== undefined) {
      updateFields.push('id_card = ?');
      updateValues.push(idCard || null);
    }
    if (gender !== undefined) {
      updateFields.push('gender = ?');
      updateValues.push(gender || null);
    }
    if (birthDate !== undefined) {
      updateFields.push('birth_date = ?');
      updateValues.push(birthDate || null);
    }
    if (phone !== undefined) {
      updateFields.push('phone = ?');
      updateValues.push(phone || null);
    }
    if (email !== undefined) {
      updateFields.push('email = ?');
      updateValues.push(email || null);
    }
    if (province !== undefined) {
      updateFields.push('province = ?');
      updateValues.push(province || null);
    }
    if (city !== undefined) {
      updateFields.push('city = ?');
      updateValues.push(city || null);
    }
    if (county !== undefined) {
      updateFields.push('county = ?');
      updateValues.push(county || null);
    }
    if (village !== undefined) {
      updateFields.push('village = ?');
      updateValues.push(village || null);
    }
    if (address !== undefined) {
      updateFields.push('address = ?');
      updateValues.push(address || null);
    }
    if (postalCode !== undefined) {
      updateFields.push('postal_code = ?');
      updateValues.push(postalCode || null);
    }
    if (status !== undefined) {
      updateFields.push('status = ?');
      updateValues.push(status);
    }

    // 添加更新时间
    updateFields.push('updated_at = NOW()');
    updateValues.push(userId);

    // 执行更新
    const [result] = await promisePool.execute(
      `UPDATE users SET ${updateFields.join(', ')} WHERE id = ?`,
      updateValues
    );

    if (result.affectedRows > 0) {
      res.json({
        success: true,
        message: '用户信息修改成功',
        data: {
          userId: userId,
          accountNumber: user.account_number,
          updatedFields: updateFields.filter(field => field !== 'updated_at = NOW()')
        }
      });
    } else {
      res.status(500).json({
        success: false,
        message: '用户信息修改失败，请重试'
      });
    }

  } catch (error) {
    console.error('管理员修改用户信息失败:', error);
    res.status(500).json({
      success: false,
      message: '服务器内部错误',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
});

// 删除用户接口（管理员专用）
router.delete('/admin/delete-user', async (req, res) => {
  try {
    // 验证请求数据
    const { error, value } = deleteUserSchema.validate(req.body);
    if (error) {
      return res.status(400).json({
        success: false,
        message: error.details[0].message
      });
    }

    const { userId, confirmDelete } = value;

    // 检查用户是否存在
    const [users] = await promisePool.execute(
      'SELECT id, username, name, role FROM users WHERE id = ? AND deleted_at IS NULL',
      [userId]
    );

    if (users.length === 0) {
      return res.status(404).json({
        success: false,
        message: '用户不存在'
      });
    }

    const user = users[0];

    // 检查是否为管理员用户
    if (user.role === 'admin') {
      return res.status(400).json({
        success: false,
        message: '不能删除管理员用户'
      });
    }

    // 开始事务
    const connection = await promisePool.getConnection();
    await connection.beginTransaction();

    try {
      // 1. 删除活动相关数据
      // 删除用户对评论的点赞记录
      await connection.execute(
        'DELETE FROM activity_comment_likes WHERE user_id = ?',
        [userId]
      );

      // 删除用户对活动的点赞记录
      await connection.execute(
        'DELETE FROM activity_likes WHERE user_id = ?',
        [userId]
      );

      // 删除用户发表的评论
      await connection.execute(
        'DELETE FROM activity_comments WHERE user_id = ?',
        [userId]
      );

      // 删除用户参与的活动记录
      await connection.execute(
        'DELETE FROM activity_participants WHERE user_id = ?',
        [userId]
      );

      // 删除用户创建的活动（需要先删除相关数据）
      const [userActivities] = await connection.execute(
        'SELECT id FROM activities WHERE created_by = ?',
        [userId]
      );

      for (const activity of userActivities) {
        // 删除活动相关的所有数据
        await connection.execute(
          'DELETE FROM activity_comment_likes WHERE comment_id IN (SELECT id FROM activity_comments WHERE activity_id = ?)',
          [activity.id]
        );
        await connection.execute(
          'DELETE FROM activity_comments WHERE activity_id = ?',
          [activity.id]
        );
        await connection.execute(
          'DELETE FROM activity_likes WHERE activity_id = ?',
          [activity.id]
        );
        await connection.execute(
          'DELETE FROM activity_participants WHERE activity_id = ?',
          [activity.id]
        );
        await connection.execute(
          'DELETE FROM activity_images WHERE activity_id = ?',
          [activity.id]
        );
        await connection.execute(
          'DELETE FROM activity_status_log WHERE activity_id = ?',
          [activity.id]
        );
        await connection.execute(
          'DELETE FROM activities WHERE id = ?',
          [activity.id]
        );
      }

      // 2. 删除投诉相关数据
      // 删除用户相关的投诉状态历史
      await connection.execute(
        'DELETE FROM complaint_status_history WHERE operator_id = ?',
        [userId]
      );

      // 删除用户的回复记录
      await connection.execute(
        'DELETE FROM complaint_replies WHERE reply_by_id = ?',
        [userId]
      );

      // 删除用户提交的投诉
      await connection.execute(
        'DELETE FROM complaints WHERE user_id = ?',
        [userId]
      );

      // 3. 删除公告相关数据
      // 删除用户的阅读记录
      await connection.execute(
        'DELETE FROM notice_read_logs WHERE user_id = ?',
        [userId]
      );

      // 删除用户发布的公告
      await connection.execute(
        'DELETE FROM notices WHERE publisher_id = ?',
        [userId]
      );

      // 4. 删除低保相关数据
      // 删除用户申请的发放记录
      await connection.execute(
        'DELETE FROM min_living_allowance_payments WHERE application_id IN (SELECT id FROM min_living_allowance_applications WHERE user_id = ?)',
        [userId]
      );

      // 删除用户申请的处理记录
      await connection.execute(
        'DELETE FROM min_living_allowance_process_logs WHERE application_id IN (SELECT id FROM min_living_allowance_applications WHERE user_id = ?)',
        [userId]
      );

      // 删除用户申请的附件
      await connection.execute(
        'DELETE FROM min_living_allowance_attachments WHERE application_id IN (SELECT id FROM min_living_allowance_applications WHERE user_id = ?)',
        [userId]
      );

      // 删除用户申请的处理进度
      await connection.execute(
        'DELETE FROM min_living_allowance_progress WHERE application_id IN (SELECT id FROM min_living_allowance_applications WHERE user_id = ?)',
        [userId]
      );

      // 删除用户申请的处理记录（操作人）
      await connection.execute(
        'DELETE FROM min_living_allowance_process_logs WHERE operator_id = ?',
        [userId]
      );

      // 删除用户申请的处理进度（操作人）
      await connection.execute(
        'DELETE FROM min_living_allowance_progress WHERE operator_id = ?',
        [userId]
      );

      // 删除用户申请的处理记录（发放操作人）
      await connection.execute(
        'DELETE FROM min_living_allowance_payments WHERE operator_id = ?',
        [userId]
      );

      // 删除用户申请的处理记录（审核人）
      await connection.execute(
        'UPDATE min_living_allowance_applications SET reviewer_id = NULL, reviewer_name = NULL WHERE reviewer_id = ?',
        [userId]
      );

      // 删除用户申请的处理记录（处理人）
      await connection.execute(
        'UPDATE min_living_allowance_applications SET processor_id = NULL, processor_name = NULL WHERE processor_id = ?',
        [userId]
      );

      // 删除用户申请的处理记录（标准创建人）
      await connection.execute(
        'UPDATE min_living_allowance_standards SET created_by = 1 WHERE created_by = ?',
        [userId]
      );

      // 删除用户的低保申请
      await connection.execute(
        'DELETE FROM min_living_allowance_applications WHERE user_id = ?',
        [userId]
      );

      // 5. 最后删除用户本身
      await connection.execute(
        'DELETE FROM users WHERE id = ?',
        [userId]
      );

      // 提交事务
      await connection.commit();

      res.json({
        success: true,
        message: '用户删除成功',
        data: {
          deletedUserId: userId,
          deletedUserName: user.name,
          deletedUserUsername: user.username
        }
      });

    } catch (error) {
      // 回滚事务
      await connection.rollback();
      throw error;
    } finally {
      // 释放连接
      connection.release();
    }

  } catch (error) {
    console.error('删除用户失败:', error);
    res.status(500).json({
      success: false,
      message: '服务器内部错误',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
});

module.exports = router;